package com.hzdracom.epubreader.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.hzdracom.epubreader.entity.BookInfoItem;

public class BookInfoDbAdapter {
//	private static final String DATABASE_NAME = "bookinfo.db";
	private static final String DATABASE_TABLE = "epub3_bookinfos";
//	private static final int DATABASE_VERSION = 1;

	private SQLiteDatabase mDb;
	@SuppressWarnings ("unused")
    private final Context mContext;

	public static final String KEY_ID = "_id";
	public static final String KEY_BOOKID = "bookid";
	public static final String KEY_BOOKNAME = "bookname";

	public static final String KEY_BOOKPATH = "bookpath";

	public static final String KEY_LASTCHAPTER = "lastchapterid";

	public static final String KEY_LASTCHARINDEX = "lastcharindex";

	public static final String KEY_COVERPATH = "coverpath";

	public static final String KEY_LASTREADTIME = "lastreadtime";

	public static final String KEY_AUTHOR = "author";

	public static final String KEY_ISTOPMOST = "istopmost";

	public static final String KEY_READPERCENT = "readpercent";
	public static final String KEY_ACCOUNT = "account";

//	private BookInfoDBOpenHelper dbOpenHelper;
	private SQLiteOpenHelper dbOpenHelper;

	public BookInfoDbAdapter(Context context,SQLiteOpenHelper sqLiteOpenHelper) {
		mContext = context;
		dbOpenHelper = sqLiteOpenHelper;
	}
//	public BookInfoDbAdapter(Context context) {
//		mContext = context;
//		dbOpenHelper = new BookInfoDBOpenHelper(context, DATABASE_NAME, null,
//		                                        DATABASE_VERSION);
//	}

	public void close() {
		mDb.close();
	}

	public void open() throws SQLiteException {
		try {
			mDb = dbOpenHelper.getWritableDatabase();
		} catch (SQLiteException ex) {
			mDb = dbOpenHelper.getReadableDatabase();
		}
	}

	// insert a new task
	public long insertTask(BookInfoItem book) {
		ContentValues newTaskValues = new ContentValues();
		// assign values for each row
		newTaskValues.put(KEY_BOOKID, book.getBookId());
		newTaskValues.put(KEY_BOOKNAME, book.getBookName());
		newTaskValues.put(KEY_BOOKPATH, book.getBookPath());
		newTaskValues.put(KEY_COVERPATH, book.getCoverPath());
		newTaskValues.put(KEY_LASTCHAPTER, book.getLastChapterId());
		newTaskValues.put(KEY_LASTCHARINDEX, book.getLastCharIndex());
		newTaskValues.put(KEY_LASTREADTIME, book.getLastReadTime().getTime());
		newTaskValues.put(KEY_AUTHOR, book.getAuthor());
		newTaskValues.put(KEY_ISTOPMOST, book.getIsTopMost());
		newTaskValues.put(KEY_READPERCENT, book.getReadPercent());
		newTaskValues.put(KEY_ACCOUNT, book.getAccount());
		// insert row
		return mDb.insert(DATABASE_TABLE, null, newTaskValues);
	}

	public boolean removeTask(/*long rowIndex*/long bookId,String account) {
		return mDb.delete(DATABASE_TABLE, KEY_BOOKID + "=" + bookId + " and " +KEY_ACCOUNT + "=" + account, null) > 0;
//		return mDb.delete(DATABASE_TABLE, KEY_ID + "=" + rowIndex, null) > 0;
	}

	public boolean isStoreBookInfo(String bookPath,String account) {
		Cursor cursor = mDb.rawQuery("select * from " + DATABASE_TABLE
				+ " where " + KEY_BOOKPATH + "='" + bookPath + "' and " +KEY_ACCOUNT + "=" + account, null);

		return cursor.getCount() > 0;
	}

	public boolean updateTask(long bookId, BookInfoItem book) {
		ContentValues newValue = new ContentValues();
		newValue.put(KEY_BOOKNAME, book.getBookName());
		newValue.put(KEY_BOOKPATH, book.getBookPath());
		newValue.put(KEY_COVERPATH, book.getCoverPath());
		newValue.put(KEY_LASTCHAPTER, book.getLastChapterId());
		newValue.put(KEY_LASTCHARINDEX, book.getLastCharIndex());
		newValue.put(KEY_LASTREADTIME, book.getLastReadTime().getTime());
		newValue.put(KEY_ISTOPMOST, book.getIsTopMost());
		newValue.put(KEY_AUTHOR, book.getAuthor());
		newValue.put(KEY_READPERCENT, book.getReadPercent());
		return mDb.update(DATABASE_TABLE, newValue, KEY_BOOKID + "=" + bookId + " and " +KEY_ACCOUNT + "=" + book.getAccount(),
				null) > 0;
	}
//	public boolean updateTask(long rowIndex, BookInfoItem book) {
//		ContentValues newValue = new ContentValues();
//		newValue.put(KEY_BOOKNAME, book.getBookName());
//		newValue.put(KEY_BOOKPATH, book.getBookPath());
//		newValue.put(KEY_COVERPATH, book.getCoverPath());
//		newValue.put(KEY_LASTCHAPTER, book.getLastChapterId());
//		newValue.put(KEY_LASTCHARINDEX, book.getLastCharIndex());
//		newValue.put(KEY_LASTREADTIME, book.getLastReadTime().getTime());
//		newValue.put(KEY_ISTOPMOST, book.getIsTopMost());
//		newValue.put(KEY_AUTHOR, book.getAuthor());
//		newValue.put(KEY_READPERCENT, book.getReadPercent());
//		return mDb.update(DATABASE_TABLE, newValue, KEY_ID + "=" + rowIndex,
//		                  null) > 0;
//	}

	public Cursor getAllItemsCursor() {
		return mDb.query(DATABASE_TABLE, new String[] { KEY_ID,KEY_BOOKID, KEY_BOOKNAME,
				KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
				KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
				KEY_READPERCENT,KEY_ACCOUNT }, null, null, null, null, null);
	}

	public Cursor getAllItemsCursorSortByReadTime() {
		return mDb.query(DATABASE_TABLE, new String[] { KEY_ID,KEY_BOOKID, KEY_BOOKNAME,
				KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
				KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
				KEY_READPERCENT,KEY_ACCOUNT }, null, null, null, null, KEY_LASTREADTIME
				+ " DESC");
	}

	public Cursor getAllItemsCursorSortByTopMostReadTime() {
		return mDb.rawQuery("select * from " + DATABASE_TABLE + " order by "
				+ KEY_ISTOPMOST + " DESC, " + KEY_LASTREADTIME + " DESC", null);
	}

	public List<BookInfoItem> getAllItems(boolean isTopmost) {
		List<BookInfoItem> result = new ArrayList<BookInfoItem>();
		Cursor cursor = null;
		if (isTopmost) {
			cursor = getAllItemsCursorSortByTopMostReadTime();
		} else {
			cursor = getAllItemsCursorSortByReadTime();
		}
		if (cursor.getCount() > 0) {

			while (cursor.moveToNext()) {
				BookInfoItem item = new BookInfoItem();
				item.setBookId(cursor.getLong(cursor.getColumnIndex(KEY_BOOKID)));
				item.setBookName(cursor.getString(cursor
						.getColumnIndex(KEY_BOOKNAME)));
				item.setBookPath(cursor.getString(cursor
						.getColumnIndex(KEY_BOOKPATH)));
				item.setCoverPath(cursor.getString(cursor
						.getColumnIndex(KEY_COVERPATH)));
				item.setLastChapterId(cursor.getString(cursor
						.getColumnIndex(KEY_LASTCHAPTER)));
				item.setLastCharIndex(cursor.getInt(cursor
						.getColumnIndex(KEY_LASTCHARINDEX)));
				item.setLastReadTime(new Date(cursor.getLong(cursor
						.getColumnIndex(KEY_LASTREADTIME))));
				item.setRecordId(cursor.getLong(cursor.getColumnIndex(KEY_ID)));
				item.setAuthor(cursor.getString(cursor
						.getColumnIndex(KEY_AUTHOR)));
				item.setReadPercent(cursor.getDouble(cursor
						.getColumnIndex(KEY_READPERCENT)));
				item.setIsTopMost(cursor.getInt(cursor
						.getColumnIndex(KEY_ISTOPMOST)));
				item.setAccount(cursor.getString(cursor.getColumnIndex(KEY_ACCOUNT)));
				result.add(item);
			}
		}

		return result;
	}

	public Cursor setCursorToToDoItem(/*long rowIndex*/long bookId) throws SQLException {
		Cursor result = mDb.query(DATABASE_TABLE, new String[] { KEY_ID,KEY_BOOKID,
				KEY_BOOKNAME, KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
				KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
				KEY_READPERCENT,KEY_ACCOUNT }, KEY_BOOKID + "=" + bookId, null, null, null,
				null);
//		Cursor result = mDb.query(DATABASE_TABLE, new String[] { KEY_ID,KEY_BOOKID,
//		                                                         KEY_BOOKNAME, KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
//		                                                         KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
//		                                                         KEY_READPERCENT }, KEY_ID + "=" + rowIndex, null, null, null,
//		                                                         null);
		if (result.getCount() == 0 || !result.moveToFirst()) {
//			throw new SQLException("No to do item found for row:" + bookId);
			return null;
		}
		return result;
	}

	public BookInfoItem getBookItem(/*long rowIndex*/long bookId,String account) throws SQLException {
		Cursor cursor = mDb.query(DATABASE_TABLE, new String[] { KEY_ID,KEY_BOOKID,
				KEY_BOOKNAME, KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
				KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
				KEY_READPERCENT,KEY_ACCOUNT }, KEY_BOOKID + "=" + bookId + " and " +KEY_ACCOUNT + "=" + account, 
				null, null, null,null);
//		Cursor cursor = mDb.query(DATABASE_TABLE, new String[] { KEY_ID,KEY_BOOKID,
//		                                                         KEY_BOOKNAME, KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
//		                                                         KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
//		                                                         KEY_READPERCENT }, KEY_ID + "=" + rowIndex, null, null, null,
//		                                                         null);
		if (cursor.getCount() == 0 || !cursor.moveToFirst()) {
//			throw new SQLException("No to do item found for row:" + bookId);
			return null;
		}
		BookInfoItem result = new BookInfoItem();
		result.setBookId(cursor.getLong(cursor.getColumnIndex(KEY_BOOKID)));
		result.setBookName(cursor.getString(cursor.getColumnIndex(KEY_BOOKNAME)));
		result.setBookPath(cursor.getString(cursor.getColumnIndex(KEY_BOOKPATH)));
		result.setCoverPath(cursor.getString(cursor
				.getColumnIndex(KEY_COVERPATH)));
		result.setLastChapterId(cursor.getString(cursor
				.getColumnIndex(KEY_LASTCHAPTER)));
		result.setLastCharIndex(cursor.getInt(cursor
				.getColumnIndex(KEY_LASTCHARINDEX)));
		result.setLastReadTime(new Date(cursor.getLong(cursor
				.getColumnIndex(KEY_LASTREADTIME))));
		result.setRecordId(cursor.getLong(cursor.getColumnIndex(KEY_ID)));
		result.setAuthor(cursor.getString(cursor.getColumnIndex(KEY_AUTHOR)));
		result.setReadPercent(cursor.getDouble(cursor
				.getColumnIndex(KEY_READPERCENT)));
		result.setIsTopMost(cursor.getInt(cursor.getColumnIndex(KEY_ISTOPMOST)));
		result.setAccount(cursor.getString(cursor.getColumnIndex(KEY_ACCOUNT)));
		return result;

	}

	public static final String DATATABLE_DROP = "DROP TABLE IF EXISTS " + DATABASE_TABLE;
	// SQL statement to create a new database
	public static final String DATATABLE_CREATE = "create table "
			+ DATABASE_TABLE + " (" + KEY_ID
			+ " integer primary key autoincrement , " + KEY_BOOKID
			+ " integer , " + KEY_BOOKNAME
			+ " text not null, " + KEY_BOOKPATH + " text not null, "
			+ KEY_LASTCHAPTER + " text not null, " + KEY_LASTCHARINDEX
			+ " integer, " + KEY_COVERPATH + " text not null, "
			+ KEY_LASTREADTIME + " long, " + KEY_AUTHOR
			+ " text not null, " + KEY_ISTOPMOST + " integer,"
			+ KEY_READPERCENT + " double,"
			+ KEY_ACCOUNT + " text not null" + 
			");";
//	private static class BookInfoDBOpenHelper extends SQLiteOpenHelper {
//
//		public BookInfoDBOpenHelper(Context context, String name,
//				CursorFactory factory, int version) {
//			super(context, name, factory, version);
//		}
//
//		@Override
//		public void onCreate(SQLiteDatabase db) {
//			db.execSQL(DATATABLE_CREATE);
//
//		}
//
//		@Override
//		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//			Log.w("TaskDBAdapter", "Upgrading from version " + oldVersion
//					+ " to " + newVersion);
//
//			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
//			onCreate(db);
//
//		}
//
//	}
}
